package cs633.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class Utils {
	
	public static List<Album> getAlbums(String userId) {
		Album album = null;		
		Connection con = null;
		List<Album> aList = new ArrayList<Album>();
		try {
			con = DBManager.getInstance().getConn();
			String sql = "SELECT B.ALBUM_NAME, B.ALBUM_LOC, B.USER_ALBUM_ID FROM USER A, USER_ALBUM B WHERE A.USER_ID = ? AND A.USER_SEQ_ID = B.USER_SEQ_ID ORDER BY B.ALBUM_NAME";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, userId);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				album = new Album();
				album.setAlbumId(rs.getInt("USER_ALBUM_ID"));
				album.setAlbumName(rs.getString("ALBUM_NAME"));
				album.setAlbumLoc(rs.getString("ALBUM_LOC"));
				aList.add(album);
			}

		} catch (Exception e) {
			System.out.println("Error: " + e.getMessage());
		} finally {
			try {
				con.close();
			} catch (Exception e) {
				
			}
		}	
		return aList;
	}
	
	public static List<Album> getPublicAlbums() {
		Album album = null;		
		Connection con = null;
		List<Album> aList = new ArrayList<Album>();
		try {
			con = DBManager.getInstance().getConn();
			String sql = "SELECT ALBUM_NAME, ALBUM_LOC, USER_ALBUM_ID FROM USER_ALBUM WHERE ALBUM_TYPE = 'U' ORDER BY ALBUM_NAME";
			PreparedStatement pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				album = new Album();
				album.setAlbumId(rs.getInt("USER_ALBUM_ID"));
				album.setAlbumName(rs.getString("ALBUM_NAME"));
				album.setAlbumLoc(rs.getString("ALBUM_LOC"));
				aList.add(album);
			}

		} catch (Exception e) {
			System.out.println("Error: " + e.getMessage());
		} finally {
			try {
				con.close();
			} catch (Exception e) {
				
			}
		}	
		return aList;
	}
	
	public static List<Photo> getPhotos(String userId, String albumName) {
		Photo photo = null;

		Connection con = null;	
		List<Photo> aList = new ArrayList<Photo>();
		
		try {
			con = DBManager.getInstance().getConn();
			String sql = "SELECT A.ALBUM_LOC, B.USER_IMG_ID, B.IMG_NAME, B.IMG_TITLE, B.IMG_DESC FROM USER_ALBUM A, USER_IMG B, USER C " +
					"WHERE A.USER_ALBUM_ID = B.USER_ALBUM_ID AND C.USER_SEQ_ID = A.USER_SEQ_ID AND C.USER_ID = ? AND A.ALBUM_NAME = ?";			
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, userId);
			pstmt.setString(2, albumName);
			
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				photo = new Photo();
				photo.setPhotoId(rs.getLong("USER_IMG_ID"));
				photo.setAlbumLoc(rs.getString("ALBUM_LOC"));
				photo.setPhotoName(rs.getString("IMG_NAME"));
				photo.setPhotoTitle(rs.getString("IMG_TITLE"));
				photo.setPhotoDesc(rs.getString("IMG_DESC"));
				
				aList.add(photo);
			}

		} catch (Exception e) {
			System.out.println("Error: " + e.getMessage());
		} finally {
			try {
				con.close();
			} catch (Exception e) {
				
			}
		}
		return aList;
	}
	
	public static List<Photo> getPublicPhotos(String albumName) {
		Photo photo = null;

		Connection con = null;	
		List<Photo> aList = new ArrayList<Photo>();
		
		try {
			con = DBManager.getInstance().getConn();
			String sql = "SELECT A.ALBUM_LOC, B.USER_IMG_ID, B.IMG_NAME, B.IMG_TITLE, B.IMG_DESC FROM USER_ALBUM A, USER_IMG B " + 
						 "WHERE A.USER_ALBUM_ID = B.USER_ALBUM_ID AND A.ALBUM_TYPE = 'U' AND A.ALBUM_NAME = ?";			
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, albumName);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				photo = new Photo();
				photo.setPhotoId(rs.getLong("USER_IMG_ID"));
				photo.setAlbumLoc(rs.getString("ALBUM_LOC"));
				photo.setPhotoName(rs.getString("IMG_NAME"));
				photo.setPhotoTitle(rs.getString("IMG_TITLE"));
				photo.setPhotoDesc(rs.getString("IMG_DESC"));
				
				aList.add(photo);
			}

		} catch (Exception e) {
			System.out.println("Error: " + e.getMessage());
		} finally {
			try {
				con.close();
			} catch (Exception e) {
				
			}
		}
		return aList;
	}
	
	public static Album getAlbum(String userID, String albumName) {
		String loc = null;
		int id = 0;
		Connection con = null;
		Album album = new Album();
		try {
			con = DBManager.getInstance().getConn();
			String sql = "SELECT USER_ALBUM_ID, ALBUM_LOC FROM USER U, USER_ALBUM A WHERE U.USER_SEQ_ID = A.USER_SEQ_ID AND U.USER_ID = ? AND A.ALBUM_NAME = ?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, userID);
			pstmt.setString(2, albumName);
			
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				loc = rs.getString("ALBUM_LOC");
				id = rs.getInt("USER_ALBUM_ID");
			}
			album.setAlbumLoc(loc);
			album.setAlbumId(id);
			con.close();
		}
		catch (Exception e) {
			System.out.println("Error: " + e.getMessage());
		} 
		finally {
			try {
				con.close();
			} catch (Exception e) {
				
			}
		}
		return album;
	}	
}
